﻿
using DbJobsApi.Dto.Oracle;
using Microsoft.AspNetCore.Mvc;
using Oracle.ManagedDataAccess.Client;
using System.Reflection;
using DbJobsApi.Extensions;
using DbJobsApi.Dto;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Logging;
using DbJobsApi;

namespace DbJobsApi.Controllers
{
    [Route("/api/[controller]/[action]")]
    [ApiController]
    public class OracleController : ControllerBase
    {
        private readonly ILogger<OracleController> logger;
        public OracleController(ILogger<OracleController> logger)
        {
            this.logger = logger;
        }

        /// <summary>
        /// 获取指定库的所有表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public PageResult<CustomTableEntity> GetTables(SearchTableEntity entity)
        {
            using var conn = new OracleConnection(entity.DbConnStr);

            var param = new Dictionary<string, object>
            {
                { ":dbName", entity.DbName }
            };
            var list = conn.OracleQuery<CustomTableEntity>(@"
SELECT 
  :dbName dbName, a.TABLE_NAME, a.TABLE_TYPE, a.COMMENTS, b.CREATED, b.LAST_DDL_TIME
FROM USER_TAB_COMMENTS a
LEFT JOIN 
(
    SELECT OBJECT_NAME,OWNER,MIN(CREATED) CREATED,MIN(LAST_DDL_TIME) LAST_DDL_TIME FROM SYS.ALL_OBJECTS
    GROUP BY OBJECT_NAME,OWNER
)
B ON A.TABLE_NAME=B.OBJECT_NAME AND :dbName =B.OWNER
WHERE a.TABLE_TYPE = 'TABLE' AND a.TABLE_NAME NOT LIKE '%=%' OR TABLE_TYPE = 'VIEW'
union all
SELECT 
 :dbName dbName, OBJECT_NAME TABLE_NAME,object_type TABLE_TYPE, null COMMENTS,CREATED,LAST_DDL_TIME
from 
USER_OBJECTS where object_type in ('PROCEDURE', 'FUNCTION')
", param);

            if (list == null)
                list = new();
            var whereList = list.AsQueryable();
            if (!string.IsNullOrEmpty(entity.TABLE_NAME))
            {
                whereList = whereList.Where(m => m.TABLE_NAME.Contains(entity.TABLE_NAME));
            }

            var records = list.Count;
            var result = new PageResult<CustomTableEntity>
            {
                rows = whereList
                        .OrderBy(entity.sidx, entity.sord)
                        .Skip((entity.page - 1) * entity.rows)
                        .Take(entity.rows).ToList(),
                records = records,
                page = entity.page,
                total = records / entity.rows + (records % entity.rows != 0 ? 1 : 0)
            };
            return result;
        }

        /// <summary>
        /// 获取指定表的结构信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        [HttpPost]
        public List<TableColumnEntity> GetTable(GetTableEntity entity)
        {
            using var conn = new OracleConnection(entity.DbConnStr);
            List<TableColumnEntity> result;
            try
            {
                var param = new Dictionary<string, object>
                {
                    { ":dbName", entity.DbName },
                    { ":tableName", entity.TABLE_NAME }
                };

                //必须使用函数获取默认值
                conn.OracleExcute(@"
create or replace function get_col_default
  (
    p_owner in all_tab_cols.owner%type,
    p_table_name in all_tab_cols.table_name%type,
    p_column_name in all_tab_cols.column_name%type
    )
    return varchar2
    as 
      l_data_default LONG;
   begin
           select data_default into l_data_default
             from all_tab_cols
            where owner = p_owner
              and table_name = p_table_name
              and column_name = p_column_name;
           RETURN SUBSTR( L_DATA_DEFAULT, 1, 4000 );
   end;");

                result = conn.OracleQuery<TableColumnEntity>(@"
SELECT 
  :dbName DbName, b.TABLE_NAME, b.COLUMN_NAME, b.DATA_TYPE, b.DATA_LENGTH, b.DATA_SCALE, b.NULLABLE, trim(get_col_default( 'IOT_LCDP_MGT', B.TABLE_NAME, B.COLUMN_NAME )) DATA_DEFAULT, c.COMMENTS, case when d.COLUMN_NAME is not null then 1 else 0 end ISKEY, d.CONSTRAINT_NAME
from ALL_TAB_COLUMNS b 
LEFT JOIN USER_COL_COMMENTS C ON B.COLUMN_NAME = C.COLUMN_NAME
LEFT JOIN
(
    select col.COLUMN_NAME, col.CONSTRAINT_NAME
    from user_constraints con,  user_cons_columns col 
    WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME 
    AND CON.CONSTRAINT_TYPE='P' AND COL.TABLE_NAME= :tableName
) d ON b.COLUMN_NAME = d.COLUMN_NAME
WHERE B.TABLE_NAME = :tableName AND C.TABLE_NAME = :tableName
ORDER BY B.COLUMN_ID", param);

            }
            catch (Exception ex)
            {
                logger.LogInformation($"{ex.Message}");
                result = new List<TableColumnEntity>();
            }
            return result;
        }

        /// <summary>
        /// 编辑表结构定义 //此方法存在注入风险
        /// </summary>
        /// <param name="entity"></param>
        [HttpPost]
        public FlagEntity Edit(CustomTableEntity entity)
        {
            using var conn = new OracleConnection(entity.DbConnStr);
            try
            {
                var tableInfo = GetTables(new()
                {
                    page = 1,
                    sidx = "TABLE_NAME",
                    rows = 1,
                    sord = "asc",
                    DbName = entity.DbName,
                    DbConnStr = entity.DbConnStr,
                    TABLE_NAME = string.IsNullOrEmpty(entity.EDIT_TABLE_NAME) ? entity.TABLE_NAME : entity.EDIT_TABLE_NAME
                }).rows;
                if (tableInfo == null || tableInfo.Count == 0)
                {
                    //建表
                    var colSql = entity.Columns.Select(m => m.COLUMN_NAME_SQL).ToList();
                    if (entity.Columns.Any(m => m.ISKEY == 1))
                    {
                        colSql.Add($"CONSTRAINT {entity.TABLE_NAME}_PK PRIMARY KEY ( {Environment.NewLine}" +
                            $"{string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME))} {Environment.NewLine}" +
                            $" ) ENABLE {Environment.NewLine}");
                    }
                    _ = conn.OracleExcute($"CREATE TABLE \"{entity.TABLE_NAME}\" ( {Environment.NewLine}{string.Join(",", colSql)} {Environment.NewLine})");

                    _ = conn.OracleExcute($"COMMENT ON TABLE \"{entity.TABLE_NAME}\" IS '{entity.COMMENTS}'");

                    foreach(var m in entity.Columns)
                    {
                        _ = conn.OracleExcute($"COMMENT ON COLUMN \"{entity.TABLE_NAME}\".\"{m.COLUMN_NAME}\" IS '{m.COMMENTS}'");
                    }
                }
                else if (tableInfo.Count == 1)
                {
                    var columns = GetTable(new() { DbConnStr = entity.DbConnStr ,DbName = entity.DbName, TABLE_NAME = entity.EDIT_TABLE_NAME });

                    //修改表名
                    if (!entity.EDIT_TABLE_NAME.Equals(entity.TABLE_NAME))
                    {
                        logger.LogInformation($"表名发生变化");
                        _ = conn.OracleExcute($"ALTER TABLE \"{entity.EDIT_TABLE_NAME}\" RENAME TO \"{entity.TABLE_NAME}\"");
                    }
                    //重命名的字段
                    var renames = entity.Columns.Where(m => !string.IsNullOrEmpty(m.EDIT_COLUMN_NAME) && !m.EDIT_COLUMN_NAME.Equals(m.COLUMN_NAME))
                                    .ToList();
                    if (renames.Count > 0)
                    {
                        logger.LogInformation($"修改字段名:{renames.Count}");
                        foreach (var m in renames)
                        {
                            _ = conn.OracleExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" RENAME COLUMN \"{m.EDIT_COLUMN_NAME}\" TO \"{m.COLUMN_NAME}\"");
                        }
                    }

                    //任一属性被修改则要alter字段
                    var edits = (from a in columns
                                join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME
                                where a.DATA_TYPE != b.DATA_TYPE ||
                                a.DATA_LENGTH != b.DATA_LENGTH ||
                                a.DATA_SCALE != b.DATA_SCALE ||
                                a.DATA_DEFAULT != b.DATA_DEFAULT ||
                                a.NULLABLE != b.NULLABLE
                                select $"MODIFY ({b.COLUMN_NAME_SQL})")
                                .ToList();
                    if (edits.Count > 0)
                    {
                        logger.LogInformation($"修改字段属性:{edits.Count}");
                        _ = conn.OracleExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" {Environment.NewLine} " +
                            $"{string.Join(Environment.NewLine, edits)}");
                    }
                    //新结构不存在则删除字段
                    var dels = (from a in columns
                                join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME into bb
                                from bbb in bb.DefaultIfEmpty()
                                where bbb == null
                                select a.COLUMN_NAME)
                               .Select(m => $"ALTER TABLE \"{entity.TABLE_NAME}\" DROP COLUMN \"{m}\"")
                               .ToList();
                    if (dels.Count > 0)
                    {
                        logger.LogInformation($"删除字段:{dels.Count}");
                        foreach (var del in dels)
                        {
                            _ = conn.OracleExcute(del, null);
                        }
                    }

                    //结构没有原始列名则是新增
                    var adds = entity.Columns.Where(m => string.IsNullOrEmpty(m.EDIT_COLUMN_NAME))
                                    .Select(m => $"ALTER TABLE \"{entity.TABLE_NAME}\" ADD ({m.COLUMN_NAME_SQL})")
                                    .ToList();
                    if (adds.Count > 0)
                    {
                        logger.LogInformation($"新增字段:{adds.Count}");
                        foreach (var add in adds)
                        {
                            _ = conn.OracleExcute(add);
                        }
                    }


                    //主键变化
                    var keyChange = !string.Join(",", columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME)).Equals(
                                string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME))
                                );
                    if (keyChange)
                    {
                        logger.LogInformation($"主键发生变化");
                        var oldKeyName = columns.FirstOrDefault(m => m.ISKEY == 1)?.CONSTRAINT_NAME;
                        if (!string.IsNullOrEmpty(oldKeyName))
                        {
                            _ = conn.OracleExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" DROP CONSTRAINT \"{oldKeyName}\"");
                        }
                        var newKeys = string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME));
                        if (!string.IsNullOrEmpty(newKeys)) 
                        {
                            _ = conn.OracleExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" {Environment.NewLine} " +
                                $"ADD CONSTRAINT \"{entity.TABLE_NAME}_PK PRIMARY\" KEY ({newKeys})");
                        }
                    }
                    //备注发生变化
                    if (tableInfo.First().COMMENTS != entity.COMMENTS)
                    {
                        logger.LogInformation($"表备注发生变化");
                        _ = conn.OracleExcute($"COMMENT ON TABLE \"{entity.TABLE_NAME}\" IS '{entity.COMMENTS}'");
                    }
                    var comments = (from b in entity.Columns
                                   join a in columns on b.EDIT_COLUMN_NAME equals a.COLUMN_NAME into aa
                                   from aaa in aa.DefaultIfEmpty()
                                   where aaa == null || aaa.COMMENTS != b.COMMENTS
                                   select b)
                                   .ToList();
                    if (comments.Count > 0)
                    {
                        logger.LogInformation($"字段备注发生变化:{comments.Count}");
                        foreach (var m in comments)
                        {
                            _ = conn.OracleExcute($"COMMENT ON COLUMN \"{entity.TABLE_NAME}\".\"{m.COLUMN_NAME}\" IS '{m.COMMENTS}'");
                        }
                    }

                }
                else
                {
                    return new() { flag = false, msg = "你看到的表不是新的，请刷新列表重新编辑" };
                }

            }
            catch (Exception ex)
            {
                return new() { flag = false, msg = ex.Message };
            }
            return new () { flag = true };
        }

        [HttpPost]
        public FlagEntity GetMetaData(GetMetaEntity entity)
        {
            using var conn = new OracleConnection(entity.DbConnStr);

            var dt = conn.OracleQueryDataTable(
                $"SELECT DBMS_METADATA.GET_DDL('{entity.Type}','{entity.Name}') FROM DUAL");

            var data = dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : null;

            if (!string.IsNullOrEmpty(data) && entity.Type == "VIEW")
            {
                var arr = data.Split("\n").ToList();
                arr.RemoveAt(0);
                arr.RemoveAt(0);
                arr.Insert(0, $"CREATE OR REPLACE VIEW \"{entity.DbName}\".\"{entity.Name}\" AS");
                data = string.Join("\n", arr);
            }

            return new FlagEntity
            {
                flag = dt.Rows.Count > 0,
                data = data,
                msg = dt.Rows.Count > 0 ? $"{entity.Name}加载成功" : "查询不到信息"
            };
        }

        [HttpPost]
        public FlagEntity RowImport(RowImportEntity entity)
        {
            //if (entity.Fields == null || entity.Fields.Count == 0 || entity.Fields.Count != entity.Rows.Count)
            //{
            //    return new() { flag = false, msg = $"字段和值不能为空，且长度必须一致" };
            //}
            int runLine = 1;
            using var conn = new OracleConnection(entity.DbConnStr);
            try
            {
                var table = GetTable(new GetTableEntity { DbName = entity.DbName, TABLE_NAME = entity.TABLE_NAME });

                var fielsCount = from a in entity.Fields
                                 join b in table on a equals b.COLUMN_NAME
                                 select 1;

                if (fielsCount.Count() != entity.Fields.Count)
                {
                    return new() { flag = false, msg = $"字段与服务器表结构有差异" };
                }

                conn.Open();
                OracleTransaction trans = null;
                try
                {
                    trans = conn.BeginTransaction();
                    var fields = string.Join(",", entity.Fields);
                    foreach (var row in entity.Rows)
                    {
                        using var cmd = conn.CreateCommand();
                        for (int i = 0; i < entity.Fields.Count; i++)
                        {
                            var col = table.FirstOrDefault(m => m.COLUMN_NAME == entity.Fields[i]);
                            if (col != null && col.DATA_TYPE == "BLOB")
                            {
                                row[i] = Convert.FromBase64String(row[i].ToString());
                            }

                            cmd.Parameters.Add(entity.Fields[i], row[i]);
                        }
                        var values = ":" + string.Join(",:", entity.Fields);

                        cmd.CommandText = $"INSERT INTO {entity.TABLE_NAME} ({fields}) VALUES ({values})";
                        LogHelper.Write($"ADO.NET SQL  {cmd.CommandText}");
                        _ = cmd.ExecuteNonQuery();

                        runLine++;
                    }
                    trans.Commit();
                }
                catch(Exception ex)
                {
                    LogHelper.Write($"表管理 导入时, 第{runLine}行, {ex.Message}");
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    trans.Dispose();
                }
            }
            catch (Exception ex)
            {
                return new() { flag = false, msg = $"表管理 导入时, 第{runLine}行, {ex.Message}" };
            }
            return new() { flag = true };
        }

        [HttpPost]
        public object RunSql(RunSqlEntity entity)
        {
            using var conn = new OracleConnection(entity.DbConnStr);

            var dts = conn.OracleQueryDataTable(entity.Sqls, out string[] logs);

            var tables = new List<object>();
            if (dts?.Length > 0)
            {
                foreach (var dt in dts)
                {
                    var columns = new List<object>();
                    for (int i = 0; i < dt?.Columns.Count; i++)
                    {
                        var col = dt.Columns[i];
                        columns.Add(new { name = col.ColumnName, type = col.DataType.Name });
                    }
                    tables.Add(new { columns, data = dt });
                }
            }

            return new
            {
                flag = true,
                tables,
                logs
            };
        }

    }
}
